<?php
class folder extends Model{
	public static function getTableName(){
		return 'folder';
	}

	public function set($object){
		$this->folder_item_type_id= !isset($object->folder_item_type_id) ? '0' : $object->folder_item_type_id;
		$this->folder_type_id= !isset($object->folder_type_id) ? '0' : $object->folder_type_id;
		$this->system_label= !isset($object->system_label) ? '' : $object->system_label;
		$this->status= !isset($object->status) ? '0' : $object->status;
		$this->created_at= !isset($object->created_at) ? '' : $object->created_at;
		$this->created_by= !isset($object->created_by) ? '' : $object->created_by;
		$this->modified_at= !isset($object->modified_at) ? '' : $object->modified_at;
		$this->modified_by= !isset($object->modified_by) ? '' : $object->modified_by;
	}

	public function findByInSql($instr)
	{
		$zh = language::getLanguageID(AC::LANGUAGE_ZH);
		$en = language::getLanguageID(AC::LANGUAGE_EN);
		$sql = '
			select tmptab.*, ft.`path` from (
			select 
				count(b.folder_id) as total,
				(SELECT title FROM `folder-language` fl WHERE folder_id = a.id AND language_id = '.$zh.') AS zh_title,
				(SELECT title FROM `folder-language` fl WHERE folder_id = a.id AND language_id = '.$en.') AS en_title, # a.system_label,
				a.id,folder_item_type_id,folder_type_id
			from 
				folder a left join 
				folder_item b on a.id=b.folder_id left join
				image i on i.id = b.item_id 
			where a.id in('.$instr.') and (i.status <> '. AC::STATUS_DELETED .' or i.status is null) and folder_item_type_id='. AC::FOLDER_TYPE_IMAGE .' group by a.id
			union all
			select 
				count(b.folder_id) as total,
				(SELECT title FROM `folder-language` fl WHERE folder_id = a.id AND language_id = '.$zh.') AS zh_title,
				(SELECT title FROM `folder-language` fl WHERE folder_id = a.id AND language_id = '.$en.') AS en_title, # a.system_label,
				a.id,folder_item_type_id,folder_type_id 
			from 
				folder a left join 
				folder_item b on a.id=b.folder_id left join
				video v on v.id = b.item_id 
			where a.id in('.$instr.') and (v.status <> '. AC::STATUS_DELETED .' or v.status is null) and folder_item_type_id='. AC::FOLDER_TYPE_VIDEO .' group by a.id
            ) as tmptab 
            LEFT JOIN `folder_type` AS ft ON ft.`id`=tmptab.`folder_type_id`
            order by folder_item_type_id , id 
			';
		//echo $sql,$instr;
		$result	= Model::selectBySql($sql);
		foreach($result as & $row){
			if(trim($row['zh_title'])){
				$row['system_label'] = $row['zh_title'];
			}else{
				$row['system_label'] = $row['en_title'];
			}
		}
		return $result;
	}

	public function get_folder_items($id){
		$param = array(':id'=>$id,':status'=>AC::STATUS_DELETED,':item_type_id'=>AC::FOLDER_TYPE_IMAGE);
		$sql = '
			SELECT 
				i.id, fil.title, i.thumbnail_url AS thumb , i.source_url AS src, i.status
			FROM 
				image i 
				INNER JOIN folder_item fi ON fi.item_id = i.id
				INNER JOIN `folder_item-language` fil ON (fi.item_id = fil.item_id) AND (fi.folder_id = fil.folder_id)
				INNER JOIN `folder` f ON (f.id = fil.folder_id) AND (f.id = fi.folder_id) AND (f.folder_item_type_id = :item_type_id)
			WHERE
				fi.folder_id = :id AND i.status <> :status
			ORDER BY fi.rank,fi.item_id,fil.language_id
		';
		if( $this->is_video_folder($id) ){
			$param[':item_type_id'] = AC::FOLDER_TYPE_VIDEO;
			$sql = '
				SELECT 
					v.id , fil.title, 
					CASE v.vi_status 
						WHEN '.AC::VIDEO_STATUS_QUEUE   .' THEN "'.AC::PDES_FLASH_ASSETS_PROCESS .'"
						WHEN '.AC::VIDEO_STATUS_WAITING .' THEN "'.AC::PDES_FLASH_ASSETS_WAIT .'" 
						WHEN '.AC::VIDEO_STATUS_FAILED  .' THEN "'.AC::PDES_FLASH_ASSETS_FAILURE .'" 
						ELSE v.thumbnail_url
					END AS thumb ,
					v.source_url AS src, v.status
				FROM 
					video v 
					INNER JOIN folder_item fi ON fi.item_id = v.id
					INNER JOIN `folder_item-language` fil ON (fi.item_id = fil.item_id) AND (fi.folder_id = fil.folder_id)
					INNER JOIN `folder` f ON (f.id = fil.folder_id) AND (f.id = fi.folder_id) AND (f.folder_item_type_id = :item_type_id)
				WHERE
					fi.folder_id = :id AND v.status <> :status
				ORDER BY fi.rank,fi.item_id,fil.language_id
			';
		}

		$data = $exists = array();
		$rs   = Model::selectBySql($sql,$param);

		foreach($rs as $row){
			if(isset($exists[$row['id']])){
				$index = $exists[$row['id']];
				$data[$index]['title'] .= ($data[$index]['title'] ? ' '.$row['title'] : $row['title']);
				continue;
			}else{
				$exists[$row['id']] = count($data);
			}
			$data[] = $row;
		}

		return $data;
	}

    public function get_folder_videos($folder_id, $page, $page_num, $order_field='modified_at', $order='ASC', $unpublish=0, $act='view')
    {
        //select db
        if ($unpublish == 1) {
            self::$dbo['r'] = CommonUtils::getDbConn(0,'preview');
        } else {
            self::$dbo['r'] = Model::$dbo['r'];
        }

		$sql = "
            SELECT v.id , TRIM(GROUP_CONCAT(fil.title ORDER BY fil.language_id SEPARATOR ' ')), v.modified_at, v.modified_by, v.vi_status, v.source_url
            FROM `folder_item` AS fi
            INNER JOIN `video` AS v ON (fi.`item_id` = v.id)
            INNER JOIN `folder_item-language` AS fil USING (folder_id, item_id) 
            WHERE `folder_id`=:folder_id AND v.status<>:status
            GROUP BY v.id 
            ";
        $param = array(
            ':folder_id'    => $folder_id,
            ':status'       => AC::STATUS_DELETED,
        );

        //total
        $sql_total = "SELECT COUNT(*) AS num FROM ({$sql}) AS tmp";
        $rs = self::selectBySql($sql_total, $param);  
        $total_record = array_shift($rs);

        //list
        $list['page'] = $page;
        $list['total'] = ceil($total_record['num'] / $page_num);
        $list['records'] = $total_record['num'];

        //order by
        $order_field = ($order_field=='title') ? 'fil.title' : 'v.'.$order_field;
        $order_by = " ORDER BY {$order_field} {$order}";

        //limit
        $start = ($page-1) * $page_num;
        $limit = " LIMIT {$start}, {$page_num}";;

        //record
        $result = self::selectBySql($sql.$order_by.$limit, $param);
        foreach ($result as $row) {
            $tmp = array();
            $tmp['id'] = $row['id'];
            $row['vi_status'] = AC::$video_status_ui[$row['vi_status']]; 
            $row['source_url'] = (strtolower($act)=='edit' ? '<a class="btn edit shortBtn videoEditBtn ui-state-default ui-corner-all" href="#edit-'.$row['id'].'" title="Edit"><span class="ui-icon ui-icon-pencil"></span>Edit</a>' : '') . (strtolower($act)=='edit' ? '<a class="btn view shortBtn ui-state-default ui-corner-all" href="#view-'.$row['id'].'" title="View"><span class="ui-icon ui-icon-image"></span>View</a>' : '<a class="btn view shortBtn ui-state-default ui-corner-all" href="get_video.html?act=view&step=2&id='.$row['id'].'" title="View"><span class="ui-icon ui-icon-image"></span>View</a>') . ($row['source_url'] ? '<a target="_blank" title="Click for Preview" href="'.$row['source_url'].'" class="preview video btn ui-state-default ui-corner-all"><span class="ui-icon ui-icon-video"/>Play</a>' : '');
            $tmp['cell'] = array_values($row);
            $list['rows'][] = $tmp;
        }
        return $list;
	}

	public function is_video_folder($id){
		return !$this->is_image_folder($id);
	}

	public function is_image_folder($id){
		$folder = Model::$dbo['r']->findInstance('folder',$id);
		$id     = $folder->folder_item_type_id;
		return $id == AC::FOLDER_TYPE_IMAGE;
	}

    /**
     * get proofread SQL 
     * @param   $id Int
     * @return  Array
     */
    public function getProofread($id,$premium=false)
    {
		if (empty($id)) return  array();

		$folder_obj = Model::$dbo['r']->findInstance('folder',$id);
		if (empty($folder_obj)) return  array();

		if($folder_obj->folder_item_type_id == AC::FOLDER_TYPE_VIDEO){
			$table = 'video';
		}elseif($folder_obj->folder_item_type_id == AC::FOLDER_TYPE_IMAGE){
			$table = 'image';
		}

        $sqlArr =   array(
                    'folder'                =>  array('id = "'. $id. '"'),
                    'folder-language'       =>  array('folder_id = "'. $id. '"'),
                );

        $sqlArray   =   array();
        foreach ($sqlArr    as  $tableName  =>  $whereArray)
        {
            $sqlArray[$tableName]   =   Model::assembleGetProofreadSql($tableName, $whereArray);
        }

		if($premium && 'video'==$table){ # add "video.premium_flag=1" to the sql
			$sqlArray['folder_item-language'] = "SELECT fil.* FROM `folder_item-language` fil INNER JOIN video o ON fil.item_id=o.id AND o.premium_flag=1 WHERE fil.folder_id=$id AND o.status<>".AC::STATUS_DELETED;
			$sqlArray['folder_item'] = "SELECT fi.* FROM folder_item fi INNER JOIN video o ON fi.item_id=o.id AND o.premium_flag=1 WHERE fi.folder_id=$id AND o.status<>".AC::STATUS_DELETED;
		}else{
			$sqlArray['folder_item-language'] = "select fil.* from `folder_item-language` fil inner join $table o ON fil.item_id=o.id where fil.folder_id=$id and o.status<>".AC::STATUS_DELETED;
			$sqlArray['folder_item'] = "select fi.* from folder_item fi inner join $table o ON fi.item_id=o.id where fi.folder_id=$id and o.status<>".AC::STATUS_DELETED;
		}
        return  $sqlArray;
    }

	/*
	 * by donald
	 * at 2011-10-25
	 */
    public function getClone($id)
    {
		if (empty($id)) return  array();

		$folder_obj = Model::$dbo['r']->findInstance('folder',$id);
		if (empty($folder_obj)) return  array();

        $sqlArr = array(
			'folder'                =>  array('id = "'. $id. '"'),
			'folder-language'       =>  array('folder_id = "'. $id. '"'),
		);

		if($folder_obj->folder_item_type_id == AC::FOLDER_TYPE_VIDEO){
			$table = 'video';
		}elseif($folder_obj->folder_item_type_id == AC::FOLDER_TYPE_IMAGE){
			$table = 'image';
			$sqlArr['image_folder-config'] = array('folder_id = "'. $id. '"');
		}

        $sqlArray = array();
        foreach ($sqlArr as  $tableName  =>  $whereArray) {
            $sqlArray[$tableName] = Model::assembleGetProofreadSql($tableName, $whereArray);
        }

		// ignore programme catchup folder's video
		if ($folder_obj->folder_type_id != AC::VIDEO_CATCHUP) {
			$sqlArray['folder_item-language'] = "select fil.* from `folder_item-language` fil inner join $table o ON fil.item_id=o.id where fil.folder_id=$id and o.status<>".AC::STATUS_DELETED;
			$sqlArray['folder_item'] = "select fi.* from folder_item fi inner join $table o ON fi.item_id=o.id where fi.folder_id=$id and o.status<>".AC::STATUS_DELETED;
		}

        return  $sqlArray;
    }

    /**
     * get image or video id from folder
     *
     * @param   $ids    folder's ID (1,2,3...)
     * @param   $item   get item type! only support image & video!
     */
    public static function getItemByFolder($ids, $item = 'image')
    {
        $result =   array();
        if (empty($ids))    return  $result;

        $itemTypeID     =   AC::FOLDER_TYPE_IMAGE;
        if ('video' ==  $item)
        {
            $itemTypeID =   AC::FOLDER_TYPE_VIDEO;
        }
        $sql=   '
            SELECT
                fi.item_id                  AS  itemID
            FROM
                folder  AS  f,  folder_item AS  fi
            WHERE
                f.id = fi.folder_id
                AND f.folder_item_type_id = "'. $itemTypeID. '"
                AND f.id IN ('. $ids .')
            ';
		$ret=   Model::object2array(Model::$dbo['r']->findBySql(__class__, $sql));

        if (isset($ret[0]['itemID']))
        {
            foreach ($ret   as  $itemValue)
            {
                $result[]   =   $itemValue['itemID'];
            }
        }
        return  $result;
    }

	/*
	 * by donald
	 * at 2011-09-22
	 */
	public static function getAll(array $params)
	{
		if (empty($params)) {
			return array();
		}

		foreach ($params as $field => $value) {
			switch ($field) {
				case 'id':
					$where[] = 'id = ?';
					$where_array[] = $value;
					break;
				case 'folder_item_type_id':
					$where[] = 'folder_item_type_id = ?';
					$where_array[] = $value;
					break;
			}
		}

		return self::$dbo['w']->find(__CLASS__, implode(' AND ', $where), $where_array);
	}

}
?>
